﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace CodeGeneration
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            code();
        }
        public string code()
        {
            List<string> table = new List<string>();
            //table.Add("sys_workshop");
            //table.Add("sys_user");
            //table.Add("sys_payment");
            //table.Add("syskindacc");
            //table.Add("fitness_checkin");
            //table.Add("pos_bill_payment");
            //table.Add("pos_sales");
            //table.Add("pos_bills");
            //table.Add("fitness_cardchange");
            //table.Add("jxc_bm_ck");
            //table.Add("memcardlog");
            //table.Add("mem_member");
            //table.Add("fitness_enroll_qchange");
            //table.Add("sys_identity");
            //table.Add("fitness_classes");
            //table.Add("mem_kind");
            //table.Add("SnArt");
            //table.Add("gym_reserve");
            //table.Add("fitness_enroll");
            //table.Add("gym_period");
            //table.Add("t_fsl_sk_student05");
            //table.Add("gym_item");
            //table.Add("fitness_monthfee");
            //table.Add("gym_kind");
            //table.Add("jxc_dj_kc_2");
            //table.Add("jxc_dj_kc_zc_2");
            //table.Add("jxc_dj_kc_db_2");
            //table.Add("jxc_dj_xs_2");
            //table.Add("jxc_dj_gh_2");
            //table.Add("jxc_bm_sp");
            //table.Add("jxc_zb_sp_kc");
            //table.Add("gym_status");
            //table.Add("card_times_info");
            //table.Add("t_fsl_Entity1");
            //table.Add("t_fsl_Entity2");
            //table.Add("fitness_memscore");
            //table.Add("t_fsl_Entity2");
            //table.Add("memcardlog");
            table.Add("t_yd_Customer");
            table.Add("sys_Cust_payment");
            table.Add("t_Customer");
            table.Add("t_fsl_custydmp_man");
            //string[] filedArr = new string[6];
            //filedArr[0] = "'billcode','billtype','rec_status','bill_status','pay_status','billshop','saleshop','payshop','billdate','opentime','closedate','closetime','openuserid','closeuserid','dailysno','tableno','tableno_ext','noofguest','serverno','periodno','shiftno','closeshift','parentshop','grand_billcode','checkin_id','cardno','memno','memid','guest_identity','name','sex','billtotal','discountpercent','discounttotal','gratuitypercent','gratuitytotal','taxpercent','taxtotal','tipstotal','rebatetotal','totalpayable','paymethod','paydesp','printno','invoiceno','invoiceflag','generateflag','payflag','reception','saletype','lockstatus','sttime','endtime','fcount_hh','fcount_mm','fcditemno','otherstatus','club_id','memsid','sequence','keyno','modify','modifytime','clubcode','Fpcno','fclub','fEntityNo','ch_payno','fcreatedate','fmodifydate','fUserNo','n_last','ifdown','is_check','bm_ck','dzxh','checker','rq_check','cardbalance','zb_bm_ck','depositall','fcusttdno','syscode','f_paykind','fRemark','saleid','paycode','accuserid','fbillnohbaccno','fhbbillflash','fRemarkdoc','quitydje','ydallje','fydbillno','finmpyj'";
            //filedArr[1] = "'saleid','billcode','grand_billcode','rec_status','sale_status','pay_status','sale_type','billshop','saleshop','payshop','produceshop','saledate','saletime','closedate','paydate','userid','itemcode','itemname1','itemname2','itemspec','saleprice','quantity','saletotal','discountpercent','discounttotal','gratuitypercent','gratuitytotal','rebatetotal','taxpercent','taxtotal','totalpayable','saleweight','stockprice','stockqty','modifier_memo','serverno','if_discount','if_gratuity','sale_prop','if_revenue','if_generate','if_other','if_invoice','closemethod','paymethod','checkin_id','orderno','seatno','printno','otherid','if_import','if_tax','pricemethod','sale_kinds','parent_kinds','sale_category','parent_category','sale_special','parent_special','stat_code','paymentid','fit_paymentid','payflag','itemid','autocdflag','otherflag','complimentary_flag','modifier','reason','kitchen_type','seat_no','is_meal','is_backup','meal_id','meal_type','is_inv','is_addtion','club_id','holidaymethod','clubcode','sttime','endtime','fcount_hh','fcount_mm','fcditemno','Fpcno','fclub','fEntityNo','fit_saleid','fcreatedate','fmodifydate','fUserNo','n_last','ifdown','xh','dzxh','dj_cb','je_cb','bm_ck','is_check','walkcust_ftype','keyno','memno','Fremark','deposit','fcusttdno','fbillNosub','disccode','spandhyacc','ch_printflag','accuserid','fdayandhousr','fmpdaycount','zbcardno','v_artno','Fmp_m_id','acc_clubkinds1','acc_clubkinds2','acc_clubkinds3'";
            //filedArr[2] = "'paymentid','billcode','rec_status','pay_status','paymethod','payparent','payamount','payforeign','payshop','paydate','payflag','payrate','payfamt','paychanges','paycardno','ExpiryDate','paysysid','payname','userid','shiftno','if_invoice','if_revenue','payno','paymemo','paytype','memid','clubcode','fit_paymentid','fEntityNo','FClub','Fpcno','fcreatedate','fmodifydate','fUserNo','n_last','ifdown','syscode','paycode','sale_kinds','sale_category','sale_special','fifrepacc','fifdhyhpay'";
            //filedArr[3] = "'sid','cardno','cname','sex','mem_status','mem_type','checkin_date','checkin_time','operator','checkin_status','if_towel','if_keyring','keyno','if_returnkey','checkouttime','memo','color','memiden','memid','workshop','expendcard','deposit','guesttype','issuecard','lockstatus','clubcode','posflag','fEntityNo','FClub','Fpcno','fcreatedate','fmodifydate','fUserNo','n_last','ifdown','fmp','ifinshoes','ifshoes','shoesdate1','fbillNo','fmpdate','shoesdate2','ifoutshoes','fgphzs','fbphzs','fsalempdttime','ifcusttype','fje','fqry','fcusttdno','fbillNosub','city_id','fmpsttdate','fmpenddate','fmpusermin','fmpdaycount','shoesdate4','fmpjeall','fyjjeall','finje','ftime_mode'";
            //filedArr[4] = "'code','name','syscode','otherkm1','otherkmname1','otherkm2','otherkmname2','paynamev','fifsysset','parentcode','sale_kinds1','fEntityNo','FClub','Fpcno'";
            //filedArr[5] = "'code','name_c','if_efficieny','type','parent_code','if_sat','name_e','if_account','account_code','memo','money_type','payment_type','bank_service','parent_type','detail_type','type_workshop','type_code','touch_disp','fifrepacc'";
            string sql;
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < table.Count; i++)
            {
                //sb.Length = 0;
                //MessageBox.Show(table[i]);
                sql = @"SELECT
                col.name AS 列名,
                typ.name as 数据类型,
                col.max_length AS 占用字节数,
                col.precision AS 数字长度,
                col.scale AS 小数位数,
                col.is_nullable AS 是否允许非空,
                col.is_identity AS 是否自增,
                case when exists 
                ( SELECT 1 
                FROM 
                sys.indexes idx 
                join sys.index_columns idxCol 
                on (idx.object_id = idxCol.object_id)
                WHERE
                idx.object_id = col.object_id
                AND idxCol.index_column_id = col.column_id
                AND idx.is_primary_key = 1
                ) THEN 1 ELSE 0 END AS 是否是主键,
                isnull(prop.[value],'-') AS 说明
                FROM
                sys.columns col left join sys.types typ 
                on (col.system_type_id = typ.system_type_id)
                left join sys.extended_properties prop
                on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
                WHERE
                col.object_id =
                (SELECT object_id FROM sys.tables WHERE name = '" + table[i] + "')";
                //sql +=
                //@"and col.name in(" + filedArr[i] +
                //@")
                sql+=@"and typ.name not like '%TYPE' and typ.name not like '%name'
                ORDER BY 列名
                ";
                SqlConnection conn = new SqlConnection("Server=.\\sql2008;Integrated Security=SSPI;database=fslets_03");
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();
                if (textBox1.Text == "3")
                sb.AppendLine("//" + table[i] + "代码");
                if (textBox1.Text == "3")
                {
                    sb.AppendLine("class " + table[i] + " : tongbu\n");
                    sb.AppendLine("{\n");
                    sb.AppendLine("    public override void fillFields(Dictionary<String, Object> reader, Dictionary<String, Object> oldReader)\n");
                    sb.AppendLine("{\n");
                }
                if (textBox1.Text == "4")
                {
                    sb.AppendLine(" if (obj[\"data_type\"].ToString() == \"" + table[i] + "\") ");
                    sb.AppendLine("{");
                   
                }
                if (textBox1.Text == "5")
                {
                    sb.AppendLine("class " + table[i] + " : tongbu\n");
                    sb.AppendLine("{\n");
                }
                sb.AppendLine("//" + table[i] + "代码");
                string type;
                string filed;
                string pk_code;
                string primary_key_name="";
                while (reader.Read())
                {
                    //MessageBox.Show(reader[1].ToString());

                    type = reader[1].ToString();
                    filed = reader[0].ToString();
                    //pk_code = reader["是否是主键"].ToString();
                    pk_code = reader["是否自增"].ToString();
                    //if (pk_code == "1")
                        if (pk_code == "True")
                    {
                        primary_key_name = filed;
                        sb.Append(" "+filed+" ");
                        continue;
                    }
                    if (textBox1.Text == "5")
                    {
                        sb.AppendLine("        public object "+filed+" { get; set; }    ");
                        continue;
                    }
                    if (textBox1.Text == "4")
                    {
                        continue;
                    }
                    if (type.Contains("ar"))
                    {
                        //sb.AppendLine("t."+filed+" = dr[\"" + filed + "\"].ToString();");
                        if (textBox1.Text == "3") sb.AppendLine("if (oldReader == null || reader[\"" + filed + "\"].ToString() != oldReader[\"" + filed + "\"].ToString()) { this.map[\"" + filed + "\"] = reader[\"" + filed + "\"].ToString(); }");
                        if (textBox1.Text == "1") sb.AppendLine("sql += \"'\"+dict[\"" + filed + "\"] + \"',\";");
                        if (textBox1.Text == "2") sb.AppendLine("if (dict.ContainsKey(\"" + filed + "\")){builder.Append(\"," + filed + "='\").Append(dict[\"" + filed + "\"]).Append(\"'\");}");
                        if (textBox1.Text == "6") sb.AppendLine(string.Format("m.{0} = dr[\"{0}\"].ToString();", filed));
                    }
                    else if (type.Contains("time"))
                    {
                        // sb.AppendLine("t." + filed + " = dr[\"" + filed + "\"].ToString();");
                        if (textBox1.Text == "3") sb.AppendLine("if (oldReader == null || reader[\"" + filed + "\"].ToString() != oldReader[\"" + filed + "\"].ToString()) { this.map[\"" + filed + "\"] = CLRTriggers.getDateTimeString(reader,\"" + filed + "\"); }");
                        if (textBox1.Text == "1") sb.AppendLine("sql += \"'\"+dict[\"" + filed + "\"] + \"',\";");
                        if (textBox1.Text == "2") sb.AppendLine("if (dict.ContainsKey(\"" + filed + "\")){builder.Append(\"," + filed + "='\").Append(dict[\"" + filed + "\"]).Append(\"'\");}");
                        if (textBox1.Text == "6") sb.AppendLine(string.Format("m.{0} = getDatetimeString(dr[\"{0}\"]);", filed));
                    }
                    else if (type.Contains("dec") || type.Contains("num") || type.Contains("int"))
                    {

                        //sb.AppendLine("t." + filed + " = getDoubleString(dr, \""+filed+"\");");
                        if (textBox1.Text == "3") sb.AppendLine("if (oldReader == null || reader[\"" + filed + "\"].ToString() != oldReader[\"" + filed + "\"].ToString()) { this.map[\"" + filed + "\"] = CLRTriggers.getDoubleString(reader,\"" + filed + "\"); }");
                        if (textBox1.Text == "1") sb.AppendLine("sql += toNull(dict[\"" + filed + "\"]) + \",\";");
                        if (textBox1.Text == "2") sb.AppendLine("if (dict.ContainsKey(\"" + filed + "\")){builder.Append(\"," + filed + "=\").Append(dict[\"" + filed + "\"]);}");
                        if (textBox1.Text == "6") sb.AppendLine(string.Format("m.{0} = getDoubleString(dr[\"{0}\"]);",filed));
                    }
                    else
                    {
                        //sb.AppendLine("t." + filed + " = getIntString(dr, \"" + filed + "\");");
                        if (textBox1.Text == "3") sb.AppendLine("if (oldReader == null || reader[\"" + filed + "\"].ToString() != oldReader[\"" + filed + "\"].ToString()) { this.map[\"" + filed + "\"] = CLRTriggers.getIntString(reader,\"" + filed + "\"); }");
                        if (textBox1.Text == "1") sb.AppendLine("sql += \"'\"+dict[\"" + filed + "\"] + \"',\";");
                        if (textBox1.Text == "2") sb.AppendLine("if (dict.ContainsKey(\"" + filed + "\")){builder.Append(\"," + filed + "=\").Append(dict[\"" + filed + "\"]);}");
                        if (textBox1.Text == "6") sb.AppendLine(string.Format("m.{0} = getIntString(dr[\"{0}\"]);", filed));
                    }
                }
                if (textBox1.Text == "3")
                {
                    sb.AppendLine("}\n");
                    sb.AppendLine("        public override void fillPK(Dictionary<string, object> reader)\n");
                    sb.AppendLine("{\n");
                    sb.AppendLine("this.map[\"" + primary_key_name + "\"] = reader[\"" + primary_key_name + "\"].ToString();\n");
                    sb.AppendLine("}\n");
                    sb.AppendLine("}\n");
                }
                if (textBox1.Text == "4")
                {
                    sb.AppendLine(" returnDict = DbHelper.handle_table(obj, \"" + primary_key_name + "\");");
                    sb.AppendLine(" returnDict[\"ver\"] = DbHelper.Ver;");
                    sb.AppendLine(" returnList.Add(returnDict);");
                    sb.AppendLine("}");
                }
                if (textBox1.Text == "5")
                {
                    sb.AppendLine("}");
                }
                sb.Append("\n");
                conn.Close();
                richTextBox1.Clear();
                richTextBox1.Text = sb.ToString();
               // MessageBox.Show(richTextBox1.Text);
            }


            return sb.ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            List<string> table = new List<string>();
            //table.Add("sys_workshop");
            //table.Add("sys_user");
            //table.Add("sys_payment");
            //table.Add("syskindacc");
            //table.Add("fitness_checkin");
            //table.Add("pos_bill_payment");
            //table.Add("pos_sales");
            //table.Add("pos_bills");
            //table.Add("fitness_cardchange");
            //table.Add("jxc_bm_ck");
            //table.Add("memcardlog");
            //table.Add("mem_member");
            //table.Add("fitness_enroll_qchange");
            //table.Add("sys_identity");
            //table.Add("fitness_classes");
            //table.Add("mem_kind");
            //table.Add("SnArt");
            //table.Add("gym_reserve");
            //table.Add("fitness_enroll");
            //table.Add("gym_period");
            //table.Add("t_fsl_sk_student05");
            //table.Add("gym_item");
            //table.Add("fitness_monthfee");
            //table.Add("gym_kind");
            //table.Add("jxc_dj_kc_2");
            //table.Add("jxc_dj_kc_zc_2");
            //table.Add("jxc_dj_kc_db_2");
            //table.Add("jxc_dj_xs_2");
            //table.Add("jxc_dj_gh_2");
            //table.Add("jxc_bm_sp");
            //table.Add("jxc_zb_sp_kc");
            //table.Add("fitness_memscore");
            //table.Add("t_fsl_Entity2");
            table.Add("t_yd_Customer");
            table.Add("sys_Cust_payment");
            table.Add("t_Customer");
            table.Add("t_fsl_custydmp_man");
            StringBuilder sb = new StringBuilder();
            string selectSql = "";
            for (int i = 0; i < table.Count; i++)
            {
                //sb.Length=0;
                string sql = @"select a.name columnname,c.name as typename, 
case when a.is_nullable = 0 then 'Not Null' else 'Null' end as nullable,a.*
from sys.columns a , sys.objects b, sys.types c
where a.object_id = b.object_id and b.name='" + table[i] + "' ";
                sql += @"and c.name not like '%TYPE' and c.name not like '%name'
and a.system_type_id=c.system_type_id order by a.column_id";
                SqlConnection conn = new SqlConnection("Server=.\\sql2008;Integrated Security=SSPI;database=kaifa");
                conn.Open();
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();
                //sb.AppendLine("//" + table[i] + "代码");
                selectSql = "select ";
                int a = 0;
                while (reader.Read())
                {
                    if (a == 0)
                        selectSql += reader[0];
                    else
                        selectSql += "," + reader[0];
                    a++;
                }
                string moban = @"#region ※" + (i + 1) + " " + table[i] + "_financial_method\n";
                moban += "[SqlTrigger(Name = @\"" + table[i] + "_financial_method\", Target = \"[dbo].[" + table[i] + "]\", Event = \"FOR INSERT, UPDATE, DELETE\")]\n";
                moban += "public static void " + table[i] + "_financial_method()\n";
                moban += "{\n";
                moban += "        string insertedSql = @\"" + selectSql + " FROM INSERTED;\";\n";
                moban += "        string deletedSql = @\"" + selectSql + " FROM DELETED;\";\n";
                moban += "        string tableName = \"" + table[i] + "\";\n";
                moban += "        string timeStampColName = \"null\";\n";
                moban += "        syncRecordToDong8(insertedSql, deletedSql, tableName, timeStampColName);\n";
                moban += "}\n";
                moban += "        #endregion\n\r";

                //string moban = "else if (tableName == \"" + table[i] + "\")\n";
                //moban += "        {\n";
                //moban += "        m = new " + table[i] + "();\n";
                //moban += "        }\n";
                sb.Append(moban);
                conn.Close();
                //richTextBox1.Clear();
                richTextBox1.Text = sb.ToString();
                //MessageBox.Show(richTextBox1.Text);

            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
//            Dictionary<string, string> dict = new Dictionary<string, string>();
//            dict.Add("sam", @"李学琨\张鑫");
//            dict.Add("ls", @"老会员转介绍2个月转送新会员pd02031+2 
//20151127请假30天");

//            string a=LitJson2.JsonMapper.ToJson(dict);
//            MessageBox.Show(a);

            return;
            List<string> list = new List<string>();
            list.Add("3601");
            list.Add("200701");
            list.Add("300001");
            list.Add("300301");
            list.Add("300401");
            list.Add("302601");
            list.Add("700501");
            list.Add("200701");
            list.Add("98702");
            list.Add("53003");
            list.Add("65103");
            list.Add("693003");
            list.Add("52003");
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < list.Count; i++)
            {
               //sb.AppendLine(string.Format("update pos_sales set sale_kinds='{0}',sale_category='{1}' where saledate>'2016-07-11' and sale_kinds='{3}'", list[i].Substring(0, list[i].Length - 2), list[i].Substring(0, list[i].Length - 2), list[i].ToString()));
                //sb.AppendLine(("123401").Substring(0, ("123401").Length - 2));
                 //sb.AppendLine(list[i].Substring(0, list[i].Length - 2));
               //sb.AppendLine("update pos_sales set sale_kinds='" + list[i].Substring(0, list[i].Length - 2) + "',sale_category='" + list[i].Substring(0, list[i].Length - 2) + "' where saledate>'2016-07-11' and sale_kinds='" + list[i] + "'");
                sb.AppendLine("update pos_bill_payment set paymethod='" + list[i].Substring(0, list[i].Length - 2) + "' where paymethod='" + list[i] + "' and paydate>'2016-07-11'");
            }
            MessageBox.Show(sb.ToString());

        }
    }
}
